# -*- coding: utf-8 -*-  

import xlsxwriter
import xlrd
from tqdm import tqdm

class XlsxHelper:
    '''Xlsx文件的辅助类'''
    
    __cell_styles = {"font_size":10,"font_family":"Arial"}
    '''输出单元格的样式'''

    def load_all_sheets(self,filename:str) -> list :
        '''读取Xlsx文件所有sheet输出[{"sheet_name": sheet.name, "data":[[]]}]'''
        result = []
        wb = xlrd.open_workbook(filename)
        # 输出全部表
        for sheet in wb.sheets():
            resultData = []
            for rownum in range(1,sheet.nrows):
                resultData.append(sheet.row_values(rownum))
            sheet_data = {"sheet_name": sheet.name, "data":resultData}
            result.append(sheet_data)
        return result
    def load_sheet(self,filename:str,sheet_name:str = None) -> list :
        '''
        读取Xlsx文件(filename:文件名，sheet_name:将要读取的sheet名称|或从0开始的索引|或不设置即全部输出)。
        输出结果：{"sheet_name": sheet.name, "data":resultData}
        '''
        result = []
        wb = xlrd.open_workbook(filename)
        # 输出全部表
        sheet = wb.sheet_by_name(sheet_name) if sheet_name is None else wb.sheets()[0]
        resultData = []
        for rownum in range(1,sheet.nrows):
            resultData.append(sheet.row_values(rownum))
        sheet_data = {"sheet_name": sheet.name, "data":resultData}
        result.append(sheet_data)
        return result



    def set_cellFormat(self,font_style:dict):
        '''设置输出时，单元格的样式'''
        if all(font_style is not None):
            self.__cell_styles = font_style

    def save_many_sheets(self,filename,sheets):
        '''将多个数据表写入新的xlsx文件（新的xlsx文件路径，数据表集合(数据格式[{name:"xxx",data:[] } ])）'''
        # 写入数据
        print(f"准备将 {len(sheets)} 个数据表，写入新的xlsx文件...")
        workbook = xlsxwriter.Workbook(filename)
        for sheet in sheets:
            sheet_name = sheet["name"]
            data = sheet["data"]

            print(f"正在写入的表名为：{sheet_name}")
            
            worksheet = workbook.add_worksheet(sheet_name)
            font = workbook.add_format(self.__cell_styles)
            for i in tqdm(range(len(data))):
                for j in range(len(data[i])):
                    worksheet.write(i, j, data[i][j], font)
        # 关闭文件流
        print("正在写入磁盘，请等待几分钟...")
        workbook.close()
        print(f"完成，文件写入成功：{filename}")

    
    def save_to_new_file(self,filename,data,sheet_name = "sheet1"):
        '''将数据写入新的xlsx文件（新的xlsx文件路径，二维数据）'''
        # 写入数据
        print(f"正在将 {len(data)} 条数据，写入新的xlsx文件...")
        workbook = xlsxwriter.Workbook(filename)
        worksheet = workbook.add_worksheet(sheet_name)
        font = workbook.add_format({"font_size":10,"font_family":"Arial"})
        for i in tqdm(range(len(data))):
            for j in range(len(data[i])):
                worksheet.write(i, j, data[i][j], font)
        # 关闭文件流
        print("正在写入磁盘，请等待几分钟...")
        workbook.close()
        print(f"完成，文件写入成功：{filename}")

if __name__ == '__main__':
    print("该模块没有独立功能，请从调用此模块的功能执行。")